package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.models.TFinanceCheckModel;
import com.iris.live.services.data.repositories.CheckFinanceRepositorySql;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by sallygo on 2017/5/23.
 */
@Repository
public class CheckFinanceRepositorySqlImpl extends BaseRepository implements CheckFinanceRepositorySql {
    private static Logger logger = LoggerFactory.getLogger(CheckFinanceRepositorySqlImpl.class);

    @Override
    public void updateFinanceCheckData(String permit, String importBatch){

        //更新实际值
        String sql = "UPDATE t_finance_check a , " +
                "( " +
                " SELECT " +
                "  c.`顾客编号` , " +
                "  c.`车架号` , " +
                "  b.`贷款人姓名` , " +
                "  a.`申请结果日期` , " +
                "  d.`金融机构` , " +
                "  d.`保证金金额` , " +
                "  d.`首付额` , " +
                "  d.`贷款额` , " +
                "  d.`贷款期数` , " +
                "  d.`金融利率` , " +
                "  b.`贴息金额` , " +
                "  e.`随车成本` , " +
                "  a.`放款日期` , " +
                "  d.`金融手续费` , " +
                "  d.`金融服务费` " +
                " FROM " +
                "  lsh金融申请附属 b " +
                " LEFT JOIN lsh金融申请 a ON a.`初始报价单号` = b.`初始报价单号` " +
                " LEFT JOIN( " +
                "  SELECT " +
                "   a.*, c.车架号 , " +
                "   c.生产号 " +
                "  FROM " +
                "   报价单表 a , " +
                "   ( " +
                "    SELECT " +
                "     MAX(a.报价编号) 报价编号 " +
                "    FROM " +
                "     报价单表 a " +
                "    GROUP BY " +
                "     a.`初始报价单号` " +
                "   ) b , " +
                "   lsh库存表 c ," +
                "   报价交车表 d " +
                "  WHERE " +
                "   c.车架号 NOT LIKE 'IRIS%' " +
                "  AND a.`报价编号` = b.`报价编号` " +
                "  AND a.`库存编号` = c.`库存编号` " +
                "  AND a.`交车` = d.`编号` " +
                "  AND DATE_FORMAT(d.`开票时间`,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m') " +
                " ) c ON a.`初始报价单号` = c.`初始报价单号` " +
                " LEFT JOIN 报价金融表 d ON c.`金融` = d.`金融编号` " +
                " LEFT JOIN 报价随车表 e ON c.`报价编号` = e.`报价编号` " +
                " AND e.`随车名称` = '贴息' " +
                " AND e.`随车类型` = '经销商' " +
                " WHERE " +
                "  c.`许可` = :permit " +
                " AND( " +
                "  a.状态 = '06' " +
                "  OR a.状态 = '07' " +
                "  OR a.状态 = '08' " +
                "  OR a.状态 = '09' " +
                "  OR a.状态 = '10' " +
                "  OR a.状态 = '11' " +
                "  OR a.状态 = '13' " +
                "  OR a.状态 = '14' " +
                "  OR( " +
                "   a.`退款金额` <> '' " +
                "   AND a.`退款金额` IS NOT NULL " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  c.车架号 " +
                ") b " +
                " SET a.db_loan_cust_name = ROUND(IFNULL(b.`贷款人姓名`,0),2), " +
                "  a.db_loan_approval_date = b.`申请结果日期` , " +
                "  a.db_finance_company = b.`金融机构` , " +
                "  a.db_deposit_payment =  ROUND(IFNULL(b.`保证金金额`,0),2) , " +
                "  a.db_down_payment = ROUND(IFNULL(b.`首付额`,0),2) , " +
                "  a.db_loan_payment = ROUND(IFNULL(b.`贷款额`,0),2) , " +
                "  a.db_loan_term = b.`贷款期数` , " +
                "  a.db_yearly_rate = ROUND(IFNULL(b.`金融利率`,0),2) , " +
                "  a.db_BMBS_discount = ROUND(IFNULL(b.`贴息金额`,0),2) , " +
                "  a.db_dealer_discount = ROUND(IFNULL(b.`随车成本`,0),2) , " +
                "  a.db_account_date = b.`放款日期` , " +
                "  a.db_finance_fee = ROUND(IFNULL(b.`金融手续费`,0),2) , " +
                "  a.db_finance_service_fee = ROUND(IFNULL(b.`金融服务费`,0),2) , " +
                "  a.compare_result = '02' " +
                "WHERE " +
                " a.vin = b.车架号 " +
                "AND a.import_batch = :batch " +
                "AND a.permit = :permit ";
        Map params = new HashMap();
        params.put("permit",permit);
        params.put("batch",importBatch);
        int result = this.executeUpdate(sql,params);
        logger.info("updateFinanceCheckData更新成功{}条数据",result);
    }

    @Override
    public void insertFinanceCheckData(String permit, String importBatch){
        //插入不存在的实际值
        String sqlInsert = "INSERT INTO t_finance_check( " +
                " `import_batch` , " +
                " `permit` , " +
                " `vin` , " +
                " `evaluation_user` , " +
                " `db_loan_cust_name` , " +
                " `db_loan_approval_date` , " +
                " `db_finance_company` , " +
                " `db_deposit_payment` , " +
                " `db_down_payment` , " +
                " `db_loan_payment` , " +
                " `db_loan_term` , " +
                " `db_yearly_rate` , " +
                " `db_BMBS_discount` , " +
                " `db_dealer_discount` , " +
                " `db_account_date` , " +
                " `db_finance_fee` , " +
                " `db_finance_service_fee` , " +
                " `compare_result` " +
                ") SELECT " +
                " DATE_FORMAT(CURDATE() , '%Y%m%d') , " +
                " :permit , " +
                " a.`车架号` , " +
                " '金融专员' , " +
                " a.`贷款人姓名` , " +
                " a.`申请结果日期` , " +
                " a.`金融机构` , " +
                " ROUND( IFNULL(a.`保证金金额` , 0) , 2) , " +
                " ROUND(IFNULL(a.`首付额` , 0) , 2) , " +
                " ROUND(IFNULL(a.`贷款额` , 0) , 2) , " +
                " a.`贷款期数` , " +
                " ROUND(IFNULL(a.`金融利率` , 0) , 2) , " +
                " ROUND(IFNULL(a.`贴息金额` , 0) , 2) , " +
                " ROUND(IFNULL(a.`随车成本` , 0) , 2) , " +
                " a.`放款日期` , " +
                " ROUND( IFNULL(a.`金融手续费` , 0) , 2) , " +
                " ROUND( IFNULL(a.`金融服务费` , 0) , 2) , " +
                " '03' " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   * " +
                "  FROM " +
                "   ( " +
                "    SELECT " +
                "     * " +
                "    FROM " +
                "     t_finance_check a " +
                "    WHERE " +
                "     a.import_batch = :batch " +
                "    AND a.permit = :permit " +
                "   ) a " +
                "  RIGHT JOIN( " +
                "   SELECT " +
                "    c.`顾客编号` , " +
                "    c.`车架号` , " +
                "    b.`贷款人姓名` , " +
                "    a.`申请结果日期` , " +
                "    d.`金融机构` , " +
                "    d.`保证金金额` , " +
                "    d.`首付额` , " +
                "    d.`贷款额` , " +
                "    d.`贷款期数` , " +
                "    d.`金融利率` , " +
                "    b.`贴息金额` , " +
                "    e.`随车成本` , " +
                "    a.`放款日期` , " +
                "    d.`金融手续费` , " +
                "    d.`金融服务费` FROM lsh金融申请附属 b LEFT JOIN lsh金融申请 a ON a.`初始报价单号` = b.`初始报价单号` LEFT JOIN( " +
                "     SELECT a.*, c.车架号 , " +
                "     c.生产号 FROM 报价单表 a , " +
                "     ( " +
                "      SELECT MAX(a.报价编号) 报价编号 FROM 报价单表 a GROUP BY a.`初始报价单号` " +
                "     ) b , " +
                "     `lsh库存表` c , " +
                "     `报价交车表` d WHERE c.车架号 NOT LIKE 'IRIS%' AND a.`报价编号` = b.`报价编号` AND a.`库存编号` = c.`库存编号` AND a.`交车` = d.`编号` " +
                "     AND DATE_FORMAT(d.`开票时间`,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m') " +
                "    ) c ON a.`初始报价单号` = c.`初始报价单号` LEFT JOIN 报价金融表 d ON c.`金融` = d.`金融编号` LEFT JOIN 报价随车表 e ON c.`报价编号` = e.`报价编号` AND e.`随车名称` = '贴息' AND e.`随车类型` = '经销商' WHERE c.`许可` = :permit AND( " +
                "     a.状态 = '06' OR a.状态 = '07' OR a.状态 = '08' OR a.状态 = '09' OR a.状态 = '10' OR a.状态 = '11' OR a.状态 = '13' OR a.状态 = '14' OR( " +
                "      a.`退款金额` <> '' AND a.`退款金额` IS NOT NULL " +
                "     ) " +
                "    ) GROUP BY c.车架号 " +
                "  ) b ON a.vin = b.车架号 WHERE ISNULL(a.vin) " +
                " ) a";
        Map params = new HashMap();
        params.put("permit",permit);
        params.put("batch",importBatch);
        int resultInsert = this.executeUpdate(sqlInsert,params);
        logger.info("insertFinanceCheckData写入成功{}条数据",resultInsert);
    }

    @Override
    public List<TFinanceCheckModel> selectFinanceCheckData(String permit, String importBatch){
        String sql = "select * from t_finance_check a where a.import_batch = :batch and a.permit = :permit ";
        Map params = new HashMap();
        params.put("permit",permit);
        params.put("batch",importBatch);
        List<TFinanceCheckModel> reList1 = this.getList(sql,params);
        List<TFinanceCheckModel> reList = this.getList(sql,params,TFinanceCheckModel.class);
        return reList;
    }

    @Override
    public List<TFinanceCheckModel> selectDBFinanceCheckData(String permit,String importBatch){
        String sql = "SELECT " +
                " :batch import_batch, " +
                " c.`许可` permit, " +
                " c.`车架号` vin, " +
                " '金融专员' `evaluation_user` , " +
                " b.`贷款人姓名` db_loan_cust_name, " +
                " DATE_FORMAT(a.`申请日期`,'%Y-%m-%d')  db_loan_approval_date, " +
                " d.`金融机构` db_finance_company, " +
                " ROUND( d.`保证金金额` , 2) db_deposit_payment, " +
                " ROUND( d.`首付额` , 2) db_down_payment, " +
                " ROUND( d.`贷款额` , 2) db_loan_payment, " +
                " IF(d.`贷款期数`='',NULL,d.`贷款期数`) db_loan_term, " +
                " ROUND( d.`金融利率` , 2) db_yearly_rate, " +
                " ROUND( b.`贴息金额` , 2) db_BMBS_discount, " +
                " ROUND( e.`随车内容` , 2) db_dealer_discount, " +
                " DATE_FORMAT(a.`放款日期`,'%Y-%m-%d') db_account_date, " +
                " ROUND( d.`金融手续费` , 2) db_finance_fee, " +
                " ROUND( d.`金融服务费` , 2) db_finance_service_fee " +
                "FROM " +
                " lsh金融申请附属 b " +
                "LEFT JOIN lsh金融申请 a ON a.`初始报价单号` = b.`初始报价单号` " +
                "LEFT JOIN( " +
                " SELECT " +
                "  a.*, c.车架号 , " +
                "  c.生产号 " +
                " FROM " +
                "  报价单表 a , " +
                "  ( " +
                "   SELECT " +
                "    MAX(a.报价编号) 报价编号 " +
                "   FROM " +
                "    报价单表 a " +
                "   GROUP BY " +
                "    a.`初始报价单号` " +
                "  ) b , " +
                "  lsh库存表 c , " +
                "  报价交车表 d " +
                " WHERE " +
                "  c.车架号 NOT LIKE 'IRIS%' " +
                " AND a.`报价编号` = b.`报价编号` " +
                " AND a.`库存编号` = c.`库存编号` " +
                " AND a.`交车` = d.`编号` " +
                " AND DATE_FORMAT(d.`开票时间`,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m') " +
                ") c ON a.`初始报价单号` = c.`初始报价单号` " +
                "LEFT JOIN 报价金融表 d ON c.`金融` = d.`金融编号` " +
                "LEFT JOIN 报价随车表 e ON c.`报价编号` = e.`报价编号` " +
                "AND e.`随车名称` = '贴息' " +
                "AND e.`随车类型` = '经销商' " +
                "WHERE " +
                " c.`许可` = :permit " +
                "AND( " +
                " a.状态 = '06' " +
                " OR a.状态 = '07' " +
                " OR a.状态 = '08' " +
                " OR a.状态 = '09' " +
                " OR a.状态 = '10' " +
                " OR a.状态 = '11' " +
                " OR a.状态 = '13' " +
                " OR a.状态 = '14' " +
                " OR( " +
                "  a.`退款金额` <> '' " +
                "  AND a.`退款金额` IS NOT NULL " +
                " ) " +
                ") " +
                "GROUP BY " +
                " c.车架号";
        Map params = new HashMap();
        params.put("permit",permit);
        params.put("batch",importBatch);
        List<TFinanceCheckModel> reList = this.getList(sql,params);
        return reList;
    }

}
